package com.zdkj.subway.check.controller.stationTiecketManager.xianWngCJXXQR.shouRuJS.reportError;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.zdkj.admin.api.entity.SysUser;
import com.zdkj.common.core.util.SpringContextHolder;
import com.zdkj.subway.check.controller.stationTiecketManager.xianWngCJXXQR.shouRuJS.TvmUtil;
import com.zdkj.subway.check.entity.SwAuTvmAmountDefference;
import com.zdkj.subway.check.entity.SwAuTvmBoxAmountDifference;
import com.zdkj.subway.check.entity.SwStTvmIncomeSettle;
import com.zdkj.subway.check.mapper.SwAuTvmAmountDefferenceMapper;
import com.zdkj.subway.check.mapper.SwAuTvmBoxAmountDifferenceMapper;
import com.zdkj.subway.check.mapper.SwStTvmIncomeSettleMapper;
import com.zdkj.subway.check.mapper.SysUserMapper;
import com.zdkj.subway.check.util.DateFormat;
import com.zdkj.subway.check.util.JdbcUtil;
import com.zdkj.subway.check.util.UserInfo;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.transaction.annotation.Transactional;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 *  tvm工具，主要是金额获取，报表差错提交入口类
 */
public class TvmReportErrorSqlUpdate {
	private static final Logger LOGGER = LoggerFactory.getLogger(TvmReportErrorSqlUpdate.class);
    /**
     * 插入收入结算
     * @return
     */
    @Transactional(rollbackFor = Exception.class)
    public static Integer insertSW_ST_TVM_INCOME_SETTLE(Map<String,Object> params) throws Exception {
        SwStTvmIncomeSettleMapper swStTvmIncomeSettleMapper=SpringContextHolder.getBean(SwStTvmIncomeSettleMapper.class);
        Integer res=0;
        List<Map<String, Object>> maps = queryWillAdd_SW_ST_TVM_INCOME_SETTLE(params);
        for(int i=0;i<maps.size();i++){
            Map<String, Object> map= maps.get(i);
            SwStTvmIncomeSettle swStTvmIncomeSettle=new SwStTvmIncomeSettle();
            swStTvmIncomeSettle.setLineCode((String) map.get("line_code"));
            swStTvmIncomeSettle.setStationCode((String)map.get("station_code"));
            swStTvmIncomeSettle.setTvmCode((String)map.get("tvm_code"));
            Date recordetime = DateFormat.panDuanDate(map.get("record_time"));
            swStTvmIncomeSettle.setRecordTime(recordetime);
            swStTvmIncomeSettle.setDelFlag("0");
            QueryWrapper<SwStTvmIncomeSettle> queryWrapper1=new QueryWrapper<>();
            queryWrapper1.setEntity(swStTvmIncomeSettle);
            List<SwStTvmIncomeSettle> swStTvmIncomeSettles = swStTvmIncomeSettleMapper.selectList(queryWrapper1);
            boolean isExsit = false;
            if(swStTvmIncomeSettles.size()>0){ //如果含有数据，则准备进行修改
                swStTvmIncomeSettle=swStTvmIncomeSettles.get(0);
                isExsit = true;
            }
            Date settleStartTime = DateFormat.panDuanDate((String)map.get("settle_start_time"));
            swStTvmIncomeSettle.setSettleStartTime(settleStartTime);
            Date settleEndTime = DateFormat.panDuanDate((String)map.get("settle_end_time"));
            swStTvmIncomeSettle.setSettleEndTime(settleEndTime);

            swStTvmIncomeSettle.setSettleTime(new Date());
            swStTvmIncomeSettle.setCashReceiptAmount((BigDecimal)map.get("cash_receipt_amount"));
			swStTvmIncomeSettle.setCheckAmount((BigDecimal)map.get("check_amount"));
            swStTvmIncomeSettle.setBillCashCashIncomeAmount((BigDecimal)map.get("bill_cash_cash_income_amount"));
            swStTvmIncomeSettle.setBillCashCashReceiptAmount((BigDecimal)map.get("bill_cash_cash_receipt_amount"));
            swStTvmIncomeSettle.setBillCashCashChangeAmount((BigDecimal)map.get("bill_cash_cash_change_amount"));

            swStTvmIncomeSettle.setItvmPaymentAmount((BigDecimal)map.get("itvm_payment_amount"));
            swStTvmIncomeSettle.setPassengerAttendantId(Integer.parseInt(String.valueOf(map.get("passenger_attendant_id"))));
            swStTvmIncomeSettle.setPassengerAttendantJobNo((String) map.get("passenger_attendant_job_no"));
            swStTvmIncomeSettle.setPassengerAttendantName((String) map.get("passenger_attendant_name"));

            swStTvmIncomeSettle.setAttendancePersonId(Integer.parseInt(String.valueOf(map.get("attendance_person_id"))));
            swStTvmIncomeSettle.setAttendancePersonJobNo((String) map.get("attendance_person_job_no"));
            swStTvmIncomeSettle.setAttendancePersonName((String) map.get("attendance_person_name"));

            Object settleDays = map.get("settle_days");
            swStTvmIncomeSettle.setSettleDays(Integer.parseInt(String.valueOf(settleDays)));
            swStTvmIncomeSettle.setDeptId(UserInfo.getDeptId());
            swStTvmIncomeSettle.setCreatorId(UserInfo.getId());
            swStTvmIncomeSettle.setCreatorName(UserInfo.getUserName());
            swStTvmIncomeSettle.setCreateTime(new Date());
            swStTvmIncomeSettle.setUpdateTime(new Date());
            swStTvmIncomeSettle.setSettleStatus((String) map.get("settle_status"));
            swStTvmIncomeSettle.setIsAddReportError((String) map.get("is_add_report_error"));


            Integer updateReprotCounts = swStTvmIncomeSettle.getUpdateReprotCounts();
            if((Boolean) params.getOrDefault("isReport",false)){ //报告
                swStTvmIncomeSettle.setUpdateReprotCounts(updateReprotCounts==null?1:updateReprotCounts+1);
            }else{
                swStTvmIncomeSettle.setUpdateReprotCounts(updateReprotCounts==null?0:updateReprotCounts);
            }



            if(isExsit){
            	swStTvmIncomeSettle.setUpdateTime(new Date());
                res=swStTvmIncomeSettleMapper.updateById(swStTvmIncomeSettle);
            }else{
                res=swStTvmIncomeSettleMapper.insertswStTvmIncomeSettle(swStTvmIncomeSettle);
            }

        }
        return res;
    }

    /**
     * 先查询要添加的数据
     * @param param
     * @return
     * @throws Exception
     */
    private static List<Map<String,Object>> queryWillAdd_SW_ST_TVM_INCOME_SETTLE(Map<String,Object> param) throws Exception {
        SysUserMapper sysUserMapper = SpringContextHolder.getBean(SysUserMapper.class);
        Object ids = param.get("ids");
        Object startSettleDate = param.get("startSettleDate");
        Object endSettleDate = param.get("endSettleDate");
        String startDate=DateFormat.ex_strDate_to_date(startSettleDate+"","yyyy-MM-dd");
        String endDate=DateFormat.ex_strDate_to_date(endSettleDate+"","yyyy-MM-dd");
        Object lineCode = param.get("lineCode");
        Object stationCode = param.get("stationCode");
        Object attendancePersonJobNo = param.get("attendancePersonJobNo");

        QueryWrapper<SysUser> queryWrapper=new QueryWrapper<>();
        queryWrapper.eq("user_code",attendancePersonJobNo);
        List<SysUser> sysUsers = sysUserMapper.selectList(queryWrapper);
        if(sysUsers==null || sysUsers.size()==0){
            throw new Exception("查询值班站长失败");
        }

        List<Map<String, Object>> maps = JdbcUtil.exeSqlQuery("select DISTINCT scd.line_code line_code," +
                "scd.station_code station_code," +
                "scd.tvm_code tvm_code,\n" +
                "'" + startSettleDate + "' settle_start_time," +
                "'" + endSettleDate + "' settle_end_time," +
                "scd.revenue_time record_time," +
                "now() settle_time, \n" +
                "(ifnull(scd.check_amount,0)  - ifnull(vsc.SUPPLEMENT_AMOUNT,0) + ifnull(vsc2.CLEAR_OUT_AMOUNT,0)) cash_receipt_amount,\n" +
			    "ifnull(scd.check_amount,0) check_amount,\n"  +
                /*
		        "ifnull(sdb.daily_cash_total_amount,0) bill_cash_cash_income_amount,\n" +
                "ifnull(sdb.receive_total_money,0) bill_cash_cash_receipt_amount,\n" +
                "ifnull(sdb.change_total_money,0) bill_cash_cash_change_amount,\n" +
                "ifnull(sdb.electronic_pay_total_amount,0) itvm_payment_amount,\n" +
                */
				"0.00 bill_cash_cash_income_amount,\n" +//TVM结账单主表的现金总数
                "0.00 bill_cash_cash_receipt_amount,\n" +//TVM结账单接收总计（硬币+纸币）
                "0.00 bill_cash_cash_change_amount,\n" +//TVM结账单纸币找零+硬币找零
				"0.00 itvm_payment_amount,\n" +
				"" + UserInfo.getId() + " passenger_attendant_id,\n" +
                "'" + UserInfo.getUserCode() + "' passenger_attendant_job_no,\n" +
                "'" + UserInfo.getName() + "' passenger_attendant_name,\n" +
                "" + sysUsers.get(0).getUserId() + " attendance_person_id,\n" +
                "'" + sysUsers.get(0).getUserCode() + "' attendance_person_job_no,\n" +
                "'" + sysUsers.get(0).getNikename() + "' attendance_person_name,\n" +
                "DATEDIFF('" + endDate + "','" + startDate + "') settle_days,\n" +
                "" + UserInfo.getDeptId() + " dept_id,\n" +
                "" + UserInfo.getId() + " creator_id,\n" +
                "'" + UserInfo.getName() + "' creator_name,\n" +
                "now() create_time,\n" +
                "now() update_time,\n" +
                "'1' settle_status,\n" +
                "'0' is_add_report_error,\n" +
                "0 update_reprot_counts,\n" +
                "'0' del_flag\n" +
                "        from sw_st_tvm_check_detail scd\n" +
               /* 去掉结账单据查询
				 "        left join \n" +
                "        (select a.line_code,a.station_code,a.tvm_code,sum(a.daily_cash_total_amount) daily_cash_total_amount,sum(a.electronic_pay_total_amount) electronic_pay_total_amount,\n" +
                "\t\t\t\t\tsum(b.RECEIVE_TOTAL_MONEY) RECEIVE_TOTAL_MONEY,sum(b.CHANGE_TOTAL_MONEY) CHANGE_TOTAL_MONEY\n" +
                "\t\t\t\t\tfrom sw_st_tvm_daily_bill a\n" +
                "\t\t\t\t\tleft join sw_st_tvm_daily_bill_cash_pay b on a.id = b.RELATION_ID and a.del_flag='0' and b.del_flag='0'\n" +
                "\t\t\t\t\twhere line_code='" + lineCode + "' and station_code='" + stationCode + "'\n" +
                "\t\t\t\t\tand (a.START_TIME between '" + startSettleDate + "' and '" + endSettleDate + "') and (a.END_TIME between '" + startSettleDate + "' and '" + endSettleDate + "')\n" +
                "\t\t\t\t\tgroup by line_code,station_code,tvm_code) sdb on scd.line_code=sdb.line_code and scd.station_code=sdb.station_code and scd.tvm_code = sdb.tvm_code\n" +
				*/
                "        left join \n" +
                "        (select LINE_CODE,STATION_CODE,TVM_CODE,sum(SUPPLEMENT_AMOUNT) SUPPLEMENT_AMOUNT\n" +
                "        \tfrom view_sw_st_tvm_supplement_currency \n" +
                "\t\t\t\t\twhere SUPPLEMENT_CURRENCY_TIME between '" + startSettleDate + "' and '" + endSettleDate + "'\n" +
                "        \tgroup by line_code,station_code,tvm_code) vsc on scd.line_code=vsc.line_code and scd.station_code=vsc.station_code and scd.tvm_code = vsc.tvm_code\n" +
                "        left join \n" +
                "        (select LINE_CODE,STATION_CODE,TVM_CODE,sum(CLEAR_OUT_AMOUNT+CLEAR_OUT_PAPER_AMOUNT) CLEAR_OUT_AMOUNT\n" +
                "        \tfrom view_sw_st_handwork_clean_record \n" +
                "\t\t\t\t\twhere CLEAN_TIME between '" + startSettleDate + "' and '" + endSettleDate + "'\n" +
                "        \tgroup by line_code,station_code,tvm_code) vsc2 on scd.line_code=vsc2.line_code and scd.station_code=vsc2.station_code and scd.tvm_code = vsc2.tvm_code\n" +
                "        left join sw_st_longperiod_settle_log sls on scd.line_code=sls.line_code and scd.station_code=sls.station_code and scd.tvm_code=sls.device_no\n" +
                "        left join sw_st_tvm_income_settle ssis on scd.line_code=ssis.line_code and scd.station_code=ssis.station_code and scd.tvm_code=ssis.tvm_code and ssis.del_flag='0'\n" +
                "        where scd.id in (" + ids + ") ");
        
        return maps;
    }


    /**
     * 插入·收入审核主表
     * @param params
     * @return
     * @throws SQLException
     * @throws ParseException
     */
    @Transactional(rollbackFor = Exception.class)
    public static Integer insert_sw_au_tvm_amount_defference(Map<String,Object> params) throws Exception {
        //使用SpringContextHolder获取bean实例
        SwAuTvmAmountDefferenceMapper swAuTvmAmountDefferenceMapper = SpringContextHolder.getBean(SwAuTvmAmountDefferenceMapper.class);
        List<Map<String, Object>> maps = queryWillAdd_sw_au_tvm_amount_defference(params);
        Integer res=0;
        for(Map<String, Object> param:maps){
            SwAuTvmAmountDefference swAuTvmAmountDefference=new SwAuTvmAmountDefference();

            swAuTvmAmountDefference.setLineCode((String) param.get("line_code"));
            swAuTvmAmountDefference.setStationCode((String) param.get("station_code"));
            swAuTvmAmountDefference.setTvmCode((String) param.get("tvm_code"));
            Date record_time = DateFormat.panDuanDate(param.get("record_time"));
            swAuTvmAmountDefference.setRecordTime(record_time);
            swAuTvmAmountDefference.setDelFlag("0");
            QueryWrapper<SwAuTvmAmountDefference> queryWrapper1=new QueryWrapper<>();
            queryWrapper1.setEntity(swAuTvmAmountDefference);
            List<SwAuTvmAmountDefference> swAuTvmAmountDefferences = swAuTvmAmountDefferenceMapper.selectList(queryWrapper1);
            boolean isExist = false;
            BigDecimal systemMoneys  = new BigDecimal("0.00");
            if(swAuTvmAmountDefferences.size()>0){
                swAuTvmAmountDefference=swAuTvmAmountDefferences.get(0);
                //存放审核表原有的系统金额
                systemMoneys = swAuTvmAmountDefference.getSystemMoney()==null?systemMoneys:swAuTvmAmountDefference.getSystemMoney();
                isExist = true;
            }
            swAuTvmAmountDefference.setIncomeSettleId((Integer) param.get("income_settle_id"));

            Date settleStartTime =DateFormat.panDuanDate((String) param.get("income_settle_begin_time"));
            Date settleEndTime =DateFormat.panDuanDate((String) param.get("income_settle_end_time"));
            settleEndTime=DateFormat.date_add_or_sub_days(settleEndTime,-1);//减一天数据

            swAuTvmAmountDefference.setIncomeSettleBeginTime(settleStartTime);
            swAuTvmAmountDefference.setIncomeSettleEndTime(settleEndTime);

            swAuTvmAmountDefference.setBoxUninstallStatus((String) param.get("box_uninstall_status"));

            swAuTvmAmountDefference.setSystemMoney((BigDecimal) param.get("system_money"));
            swAuTvmAmountDefference.setTvmBillOrderMoney((BigDecimal) param.get("tvm_bill_order_money"));


            swAuTvmAmountDefference.setTvmBillReceivePapaer((BigDecimal)param.get("tvm_bill_receive_papaer"));//TVM结账单据接收纸币金额
            swAuTvmAmountDefference.setTvmBillReceiveCoins((BigDecimal)param.get("tvm_bill_receive_coins"));//TVM结账单据接收硬币金额
            swAuTvmAmountDefference.setTvmBillNoteChange((BigDecimal)param.get("tvm_bill_note_change"));//TVM结账单据纸币找零金额
            swAuTvmAmountDefference.setTvmBillCoinChange((BigDecimal)param.get("tvm_bill_coin_change"));//TVM结账单据硬币找零金额
            swAuTvmAmountDefference.setTvmBillCashSellTicket((BigDecimal)param.get("tvm_bill_cash_sell_ticket"));//TVM结账单据现金售票金额
            swAuTvmAmountDefference.setTvmBillCashRecharge((BigDecimal)param.get("tvm_bill_cash_recharge"));//TVM结账单据现金充值金额
            swAuTvmAmountDefference.setTvmBillCashReturn((BigDecimal)param.get("tvm_bill_cash_return"));//TVM结账单据现金退款金额

            swAuTvmAmountDefference.setTvmBillElecSellTicket((BigDecimal)param.get("tvm_bill_elec_sell_ticket"));
            swAuTvmAmountDefference.setTvmBillElecRecharge((BigDecimal)param.get("tvm_bill_elec_recharge"));
            swAuTvmAmountDefference.setTvmBillElecReturn((BigDecimal)param.get("tvm_bill_elec_return"));
            if(!isExist){//不存在，再从接口中重新获取相关值
                //票款差异的sc现金
            	Map<String, Object> sc_amount = TvmUtil.getPiaoKuanCY_sc_lc_acc_cash_report(params,"sc");
                swAuTvmAmountDefference.setScCashSellTicket((BigDecimal) sc_amount.get("sale_txnamtall"));
                swAuTvmAmountDefference.setScCashRecharge((BigDecimal) sc_amount.get("recharge_txnamtall"));
                swAuTvmAmountDefference.setScCashReturn((BigDecimal) sc_amount.get("refund_txnamtall"));

                //票款差异的lc 现金
                Map<String, Object> lc_amount = TvmUtil.getPiaoKuanCY_sc_lc_acc_cash_report(params,"lc");
                swAuTvmAmountDefference.setLcCashSellTicket((BigDecimal) lc_amount.get("sale_txnamtall"));
                swAuTvmAmountDefference.setLcCashRecharge((BigDecimal) lc_amount.get("recharge_txnamtall"));
                swAuTvmAmountDefference.setLcCashReturn((BigDecimal) lc_amount.get("refund_txnamtall"));

                //票款差异的acc现金
                Map<String, Object> acc_amount = TvmUtil.getPiaoKuanCY_sc_lc_acc_cash_report(params,"acc");
                swAuTvmAmountDefference.setAccCashSellTicket((BigDecimal) acc_amount.get("sale_txnamtall"));
                swAuTvmAmountDefference.setAccCashRecharge((BigDecimal) acc_amount.get("recharge_txnamtall"));
                swAuTvmAmountDefference.setAccCashReturn((BigDecimal) acc_amount.get("refund_txnamtall"));

                //票款差异的sc电子
                Map<String, Object> sc_ele_amount = TvmUtil.getPiaoKuanCY_sc_lc_acc_ele(params,"sc");
                swAuTvmAmountDefference.setScElecSellTicket((BigDecimal) sc_ele_amount.get("sale_txnamtall"));
                swAuTvmAmountDefference.setScElecRecharge((BigDecimal) sc_ele_amount.get("recharge_txnamtall"));
                swAuTvmAmountDefference.setScElecReturn((BigDecimal) sc_ele_amount.get("refund_txnamtall"));

                //票款差异的lc电子
                Map<String, Object> lc_ele_amount = TvmUtil.getPiaoKuanCY_sc_lc_acc_ele(params,"lc");
                swAuTvmAmountDefference.setLcElecSellTicket((BigDecimal) lc_ele_amount.get("sale_txnamtall"));
                swAuTvmAmountDefference.setLcElecRecharge((BigDecimal) lc_ele_amount.get("recharge_txnamtall"));
                swAuTvmAmountDefference.setLcElecReturn((BigDecimal) lc_ele_amount.get("refund_txnamtall"));

                //票款差异的acc电子
                Map<String, Object> acc_ele_amount = TvmUtil.getPiaoKuanCY_sc_lc_acc_ele(params,"acc");
                swAuTvmAmountDefference.setAccElecSellTicket((BigDecimal) acc_ele_amount.get("sale_txnamtall"));
                swAuTvmAmountDefference.setAccElecRecharge((BigDecimal) acc_ele_amount.get("recharge_txnamtall"));
                swAuTvmAmountDefference.setAccElecReturn((BigDecimal) acc_ele_amount.get("refund_txnamtall"));
            	
            }
            


            swAuTvmAmountDefference.setSupplementMoney((BigDecimal)param.get("supplement_money"));
            swAuTvmAmountDefference.setTvmUnloadMoney(new BigDecimal((long)param.get("tvm_unload_money")));
            swAuTvmAmountDefference.setActualMoney((BigDecimal)param.get("actual_money"));//清点-补币+手工清出
            swAuTvmAmountDefference.setHandOutMoney((BigDecimal)param.get("hand_out_money"));
            swAuTvmAmountDefference.setDiffMoney((BigDecimal)param.get("diff_money"));

            swAuTvmAmountDefference.setStatementDiffAmount((BigDecimal)param.get("statement_diff_amount"));


            
            swAuTvmAmountDefference.setSysDataSource("1");//调账差异

            swAuTvmAmountDefference.setSettleBillCashId(0);
            swAuTvmAmountDefference.setSettleBillElecId(0);

            

            swAuTvmAmountDefference.setUpdateJobNo(UserInfo.getUserCode());
            swAuTvmAmountDefference.setUpdateJobName(UserInfo.getName());
            swAuTvmAmountDefference.setUpdateTime(new Date());
           

            if(isExist){  //如果存在，保留原有的系统金额，SC、LC、ACC接口金额，系统金额保留原有，差异金额根据数据来源判断
            	//swAuTvmAmountDefference.setSystemMoney(swAuTvmAmountDefference.getSystemMoney());

                Map<String, Object> sc_amount = TvmUtil.getPiaoKuanCY_sc_lc_acc_cash_report(params,"sc");
                swAuTvmAmountDefference.setScCashSellTicket((BigDecimal) sc_amount.get("sale_txnamtall"));
                swAuTvmAmountDefference.setScCashRecharge((BigDecimal) sc_amount.get("recharge_txnamtall"));
                swAuTvmAmountDefference.setScCashReturn((BigDecimal) sc_amount.get("refund_txnamtall"));
                BigDecimal scCashSellTicket =(BigDecimal) sc_amount.get("sale_txnamtall");
                BigDecimal scCashRecharge = (BigDecimal) sc_amount.get("recharge_txnamtall");
                BigDecimal scCashReturn = (BigDecimal) sc_amount.get("refund_txnamtall");
                BigDecimal add = scCashSellTicket.add(scCashRecharge).add(scCashReturn);

                Map<String, Object> lc_amount = TvmUtil.getPiaoKuanCY_sc_lc_acc_cash_report(params,"lc");
                swAuTvmAmountDefference.setLcCashSellTicket((BigDecimal) lc_amount.get("sale_txnamtall"));
                swAuTvmAmountDefference.setLcCashRecharge((BigDecimal) lc_amount.get("recharge_txnamtall"));
                swAuTvmAmountDefference.setLcCashReturn((BigDecimal) lc_amount.get("refund_txnamtall"));
                Map<String, Object> acc_amount = TvmUtil.getPiaoKuanCY_sc_lc_acc_cash_report(params,"acc");
                swAuTvmAmountDefference.setAccCashSellTicket((BigDecimal) acc_amount.get("sale_txnamtall"));
                swAuTvmAmountDefference.setAccCashRecharge((BigDecimal) acc_amount.get("recharge_txnamtall"));
                swAuTvmAmountDefference.setAccCashReturn((BigDecimal) acc_amount.get("refund_txnamtall"));
            	if("2".equals(swAuTvmAmountDefference.getDataSource())){//原来的数据来源就是SC，更新差异金额
            	    //系统金额重新获取
                    swAuTvmAmountDefference.setSystemMoney(add);
                    //重新计算差异
            		swAuTvmAmountDefference.setDiffMoney(swAuTvmAmountDefference.getActualMoney().subtract(swAuTvmAmountDefference.getSystemMoney()));
                }else{
            	    //除数据来源为sc的系统金额不变
                    swAuTvmAmountDefference.setSystemMoney(systemMoneys);
                    //重新计算差异
                    swAuTvmAmountDefference.setDiffMoney(swAuTvmAmountDefference.getActualMoney().subtract(systemMoneys));
                }
            	//更新TVM票款差异审核
                res=swAuTvmAmountDefferenceMapper.updateById(swAuTvmAmountDefference);
                
            }else{
            	swAuTvmAmountDefference.setDataSource(String.valueOf(param.get("data_source")));
            	
            	swAuTvmAmountDefference.setAuditStatus(String.valueOf(param.get("audit_status")));
                swAuTvmAmountDefference.setAssistInvestStatus(String.valueOf(param.get("assist_invest_status")));
                swAuTvmAmountDefference.setIsAddSettleorderAssist(String.valueOf(param.get("is_add_settleorder_assist")));
                swAuTvmAmountDefference.setIsAddPricediffAssist(String.valueOf(param.get("is_add_pricediff_assist")));
                swAuTvmAmountDefference.setIsAddScLevelAssist(String.valueOf(param.get("is_add_sc_level_assist")));
                swAuTvmAmountDefference.setIsAddLcLevelAssist(String.valueOf(param.get("is_add_lc_level_assist")));
                swAuTvmAmountDefference.setIsAddAccLevelAssist(String.valueOf(param.get("is_add_acc_level_assist")));
                swAuTvmAmountDefference.setIsAddSinglerAssist(String.valueOf(param.get("is_add_singler_assist")));
                swAuTvmAmountDefference.setCreatorId(UserInfo.getId());
                swAuTvmAmountDefference.setCreatorName(UserInfo.getName());
                swAuTvmAmountDefference.setCreateTime(new Date());
                //插入TVM票款差异审核
                res=swAuTvmAmountDefferenceMapper.insert(swAuTvmAmountDefference);
            }
        }

        return res;
    }


    /**
     * 先查询要添加的票款差异审核主表数据
     * @param param
     * @return
     * @throws Exception
     */
    private static List<Map<String,Object>> queryWillAdd_sw_au_tvm_amount_defference(Map<String,Object> param) throws Exception {
        Object ids = param.get("ids");
        Object startSettleDate = param.get("startSettleDate");
        Object endSettleDate = param.get("endSettleDate");
        String startDate=DateFormat.ex_strDate_to_date(startSettleDate+"","yyyy-MM-dd");
        String endDate=DateFormat.ex_strDate_to_date(endSettleDate+"","yyyy-MM-dd");
        Object lineCode = param.get("lineCode");
        Object stationCode = param.get("stationCode");
        String sql=" select \n" +
                "'#{startDate}' income_settle_begin_time,\n" +
                "'#{endDate}' income_settle_end_time,\n" +
                "  scd.ID income_settle_id,\n" +
                "  scd.LINE_CODE line_code,\n" +
                "  scd.STATION_CODE station_code,\n" +
                "  scd.TVM_CODE tvm_code,\n" +
                "  case b.UNLOAD_STATUS when '0' then '1' else '2' end as box_uninstall_status,\n" +
                "  ifnull(scd.BILL_CASH_CASH_INCOME_AMOUNT,0)*100 system_money,\n" +
                "  ifnull(scd.BILL_CASH_CASH_INCOME_AMOUNT,0)*100 tvm_bill_order_money,\n" +
                "  0 tvm_unload_money,\n" +
            	"  0.00 tvm_bill_receive_papaer,\n" +
                "  0.00 tvm_bill_receive_coins,\n" +
                "  0.00 tvm_bill_note_change,\n" +
                "  0.00 tvm_bill_coin_change,\n" +
                "  0.00 tvm_bill_cash_sell_ticket,\n" +
                "  0.00 tvm_bill_cash_recharge,\n" +
                "  0.00 tvm_bill_cash_return,\n" +
                "  0.00 tvm_bill_elec_sell_ticket,\n" +
                "  0.00 tvm_bill_elec_recharge,\n" +
                "  0.00 tvm_bill_elec_return,\n" +
                "  ifnull(vsc.SUPPLEMENT_AMOUNT,0)*100 supplement_money,\n" +
                "  ifnull(scd.CASH_RECEIPT_AMOUNT,0)*100 actual_money,\n" +
                "  ifnull(vsc2.CLEAR_OUT_AMOUNT,0)*100 hand_out_money,\n" +
                "  ifnull(scd.CASH_RECEIPT_AMOUNT,0)*100 - ifnull(scd.BILL_CASH_CASH_INCOME_AMOUNT,0)*100 as diff_money,\n" +
                "  0.00 statement_diff_amount,\n" +//原来是TVM清点金额-结账单金额算差异，实际是显示结账单自身差异  现在默认为0
                "  scd.RECORD_TIME record_time,\n" +
                "  '2' data_source,\n" +
                "  0 settle_bill_cash_id,\n" +
                "  0 settle_bill_elec_id,\n" +
                "  '0' audit_status,\n" +
                "  '0' assist_invest_status,\n" +
                "  '0' is_add_settleorder_assist,\n" +
                "  '0' is_add_pricediff_assist,\n" +
                "  '0' is_add_sc_level_assist,\n" +
                "  '0' is_add_lc_level_assist,\n" +
                "  '0' is_add_acc_level_assist,\n" +
                "  '0' is_add_singler_assist,\n" +
                "'"+ UserInfo.getId() +"' creator_id,\n" +
                "'"+ UserInfo.getName() +"' creator_name,\n" +
                "  now() create_time\n" +
                "from SW_ST_TVM_INCOME_SETTLE scd\n" +
                "left join view_sw_st_tvm_check_detail b on scd.line_code=b.line_code and scd.station_code=b.station_code and scd.tvm_code = b.tvm_code and scd.RECORD_TIME = b.REVENUE_TIME\n" +
               /*
				"left join \n" +
                "        (select a.line_code,a.station_code,a.tvm_code,\n" +
                "\t\t\t\t\tsum(b.RECEIVE_NOTES) RECEIVE_NOTES,sum(b.RECEIVE_COIN) RECEIVE_COIN,sum(b.NOTE_CHANGEN) NOTE_CHANGE,sum(b.COIN_CHANGE) COIN_CHANGE,\n" +
                "\t\t\t\t\tsum(b.FAULT_REFUND_AMOUNT) FAULT_REFUND_AMOUNT,sum(b.SELL_TICKET_TOTAL_MONEY) SELL_TICKET_TOTAL_MONEY,sum(b.RECHARGE_MONEY) RECHARGE_MONEY,\n" +
                "\t\t\t\t\tsum(c.FAULT_REFUND_AMOUNT) ELEC_FAULT_REFUND_AMOUNT,sum(c.SELL_TICKET_MONEY) ELEC_SELL_TICKET_TOTAL_MONEY,sum(c.RECHARGE_MONEY) ELEC_RECHARGE_MONEY\n" +
                "\t\t\t\t\tfrom sw_st_tvm_daily_bill a\n" +
                "\t\t\t\t\tleft join sw_st_tvm_daily_bill_cash_pay b on a.id = b.RELATION_ID and a.del_flag='0' and b.del_flag='0'\n" +
                "\t\t\t\t\tleft join sw_st_tvm_daily_bill_electronic_pay c on a.id = c.RELATION_ID and c.del_flag='0'\n" +
                "\t\t\t\t\twhere line_code='#{lineCode}' and station_code='#{stationCode}'\n" +
                "\t\t\t\t\tand (a.START_TIME between '#{startSettleDate}' and '#{endSettleDate}') and (a.END_TIME between '#{startSettleDate}' and '#{endSettleDate}')\n" +
                "\t\t\t\t\tgroup by line_code,station_code,tvm_code) sdb on scd.line_code=sdb.line_code and scd.station_code=sdb.station_code and scd.tvm_code = sdb.tvm_code\n" +*/
                "left join \n" +
                "        (select LINE_CODE,STATION_CODE,TVM_CODE,sum(SUPPLEMENT_AMOUNT) SUPPLEMENT_AMOUNT\n" +
                "        \tfrom view_sw_st_tvm_supplement_currency \n" +
                "\t\t\t\t\twhere SUPPLEMENT_CURRENCY_TIME between '#{startSettleDate}' and '#{endSettleDate}'\n" +
                "        \tgroup by line_code,station_code,tvm_code) vsc on scd.line_code=vsc.line_code and scd.station_code=vsc.station_code and scd.tvm_code = vsc.tvm_code\n" +
                "left join \n" +
                "        (select LINE_CODE,STATION_CODE,TVM_CODE,sum(CLEAR_OUT_AMOUNT+CLEAR_OUT_PAPER_AMOUNT) CLEAR_OUT_AMOUNT\n" +
                "        \tfrom view_sw_st_handwork_clean_record \n" +
                "\t\t\t\t\twhere CLEAN_TIME between '#{startSettleDate}' and '#{endSettleDate}'\n" +
                "        \tgroup by line_code,station_code,tvm_code) vsc2 on scd.line_code=vsc2.line_code and scd.station_code=vsc2.station_code and scd.tvm_code = vsc2.tvm_code\n" +
                "where b.id in ("+ids+") and scd.del_flag='0'";
        sql=sql.replace("#{startDate}",startDate).replace("#{endDate}",endDate);
        sql=sql.replace("#{startSettleDate}",startSettleDate+"").replace("#{endSettleDate}",endSettleDate+"");
        sql=sql.replace("#{lineCode}",lineCode+"").replace("#{stationCode}",stationCode+"");
        LOGGER.info("查詢TVM收入审核主表预处理SQL----"+sql);
        List<Map<String, Object>> maps = JdbcUtil.exeSqlQuery(sql);
        return maps;
    }


    /**
     * 插入·收入审核分钱箱主表
     * @param params
     * @return
     * @throws SQLException
     * @throws ParseException
     */
    @Transactional(rollbackFor = Exception.class)
    public static Integer insert_sw_au_tvm_box_amount_difference(Map<String,Object> params) throws SQLException, ParseException {
        SwAuTvmBoxAmountDifferenceMapper swAuTvmBoxAmountDifferenceMapper = SpringContextHolder.getBean(SwAuTvmBoxAmountDifferenceMapper.class);

        //查询分钱箱的数据
        List<Map<String, Object>> maps = queryWillAdd_sw_au_tvm_box_amount_difference(params,"sw_st_tvm_check_detail_coin");
        maps.addAll(queryWillAdd_sw_au_tvm_box_amount_difference(params,"sw_st_tvm_check_detail_coin_change"));
        maps.addAll(queryWillAdd_sw_au_tvm_box_amount_difference(params,"sw_st_tvm_check_detail_paper"));
        maps.addAll(queryWillAdd_sw_au_tvm_box_amount_difference(params,"sw_st_tvm_check_detail_paper_change"));
		maps.addAll(queryWillAdd_sw_au_tvm_box_amount_difference(params,"sw_st_tvm_check_detail_waste"));
        Integer res=0;
        for(Map<String, Object> param:maps){
            SwAuTvmBoxAmountDifference swAuTvmBoxAmountDifference=new SwAuTvmBoxAmountDifference();

            swAuTvmBoxAmountDifference.setLineCode(String.valueOf(param.get("line_code")));
            swAuTvmBoxAmountDifference.setStationCode(String.valueOf(param.get("station_code")));
            swAuTvmBoxAmountDifference.setTvmCode(String.valueOf(param.get("tvm_code")));
            Date record_time = DateFormat.panDuanDate(param.get("record_time"));
            swAuTvmBoxAmountDifference.setRecordTime(record_time);
			String cashBoxType = String.valueOf(param.get("cash_box_type"));
            swAuTvmBoxAmountDifference.setCashBoxType(cashBoxType);
            swAuTvmBoxAmountDifference.setDelFlag("0");
            QueryWrapper<SwAuTvmBoxAmountDifference> queryWrapper=new QueryWrapper<>();
            queryWrapper.setEntity(swAuTvmBoxAmountDifference);
            List<SwAuTvmBoxAmountDifference> swAuTvmBoxAmountDifferences = swAuTvmBoxAmountDifferenceMapper.selectList(queryWrapper);
            //默认sc
            String dataSource = "2";
			boolean isExist = false;
			//boolean isChangeCashBox =false;//再次提交时是否是钱箱编号发生了变化
			//String changeOldCashBoxCode = "";//再次提交时，原有的钱箱编号
			
			if(swAuTvmBoxAmountDifferences.size()>0){
                swAuTvmBoxAmountDifference=swAuTvmBoxAmountDifferences.get(0);
				dataSource = swAuTvmBoxAmountDifference.getDataSource();
				isExist = true;
            }
			/*else{//不是新增时，需要保留原有值的内容
			   //改变钱箱编号时的额外处理，也按修改方式处理
			   Map<String,Object> mapChangeCashBoxNum = isChangeCashBoxNum(param);
			   if(mapChangeCashBoxNum !=null &&mapChangeCashBoxNum.size()>0){
			      isChangeCashBox = true;
                  isExist = true;
				  changeOldCashBoxCode = String.valueOf(mapChangeCashBoxNum.get("changeCashBoxCode"));
				  swAuTvmBoxAmountDifference.setId((Integer)mapChangeCashBoxNum.get("id"));
			   }
			   swAuTvmBoxAmountDifference.setBillOrderMoney((BigDecimal) param.get("bill_order_money"));
			}*/
            //改变钱箱编号时的额外处理，也按修改方式处理
            //Map<String,Object> mapChangeCashBoxNum = isChangeCashBoxNum(param);
            /*if(mapChangeCashBoxNum !=null &&mapChangeCashBoxNum.size()>0){
                isChangeCashBox = true;
                isExist = true;
                changeOldCashBoxCode = String.valueOf(mapChangeCashBoxNum.get("changeCashBoxCode"));
                swAuTvmBoxAmountDifference.setId((Integer)mapChangeCashBoxNum.get("id"));
            }*/
            //swAuTvmBoxAmountDifference.setBillOrderMoney((BigDecimal) param.get("bill_order_money"));
            swAuTvmBoxAmountDifference.setDataSource(dataSource);
            Date settleStartTime =DateFormat.panDuanDate(param.get("income_settle_begin_time"));
            Date settleEndTime =DateFormat.panDuanDate(param.get("income_settle_end_time"));
            settleEndTime=DateFormat.date_add_or_sub_days(settleEndTime,-1);//减一天数据
            swAuTvmBoxAmountDifference.setIncomeSettleBeginTime(settleStartTime);
            swAuTvmBoxAmountDifference.setIncomeSettleEndTime(settleEndTime);
            swAuTvmBoxAmountDifference.setIncomeSettleId((Integer) param.get("income_settle_id"));

            swAuTvmBoxAmountDifference.setRelationid(Integer.parseInt(String.valueOf(param.get("relationid"))));
            swAuTvmBoxAmountDifference.setCashBoxRelationId(Integer.parseInt(String.valueOf(param.get("cash_box_relation_id"))));


            //swAuTvmBoxAmountDifference.setCashBoxCode(String.valueOf(param.get("cash_box_code")));
            swAuTvmBoxAmountDifference.setBoxUninstallStatus(String.valueOf(param.get("box_uninstall_status")));

            swAuTvmBoxAmountDifference.setSystemMoney((BigDecimal) param.get("system_money"));
            

       

            swAuTvmBoxAmountDifference.setUnloadDisplayMoney((BigDecimal) param.get("unload_display_money"));
            swAuTvmBoxAmountDifference.setActualMoney((BigDecimal) param.get("actual_money"));
            swAuTvmBoxAmountDifference.setSupplementMoney((BigDecimal) param.get("supplement_money"));
            swAuTvmBoxAmountDifference.setHandOutMoney((BigDecimal) (param.get("hand_out_money")));

            swAuTvmBoxAmountDifference.setDiffMoney((BigDecimal) param.get("diff_money"));

            swAuTvmBoxAmountDifference.setAuditStatus(String.valueOf(param.get("audit_status")));
            swAuTvmBoxAmountDifference.setAssistInvestStatus(String.valueOf(param.get("assist_invest_status")));
            swAuTvmBoxAmountDifference.setIsAddAssist(String.valueOf(param.get("is_add_assist")));
            swAuTvmBoxAmountDifference.setIsAddLevelAssist(String.valueOf(param.get("is_add_level_assist")));
            swAuTvmBoxAmountDifference.setCreatorId(Integer.parseInt(String.valueOf(param.get("creator_id"))));
            swAuTvmBoxAmountDifference.setCreatorName(String.valueOf(param.get("creator_name")));
            swAuTvmBoxAmountDifference.setCreateTime(new Date());

            swAuTvmBoxAmountDifference.setUpdateJobNo(UserInfo.getUserCode());
            swAuTvmBoxAmountDifference.setUpdateJobName(UserInfo.getName());
            swAuTvmBoxAmountDifference.setUpdateTime(new Date());

			//重新从接口取SC实点 金额，setSystemMoney，相当于做数据来源替换 分钱箱审核数据来源替换  需要调整---------2021-03-13-----
			/*
			     public Result getClearOutAmount(@RequestParam Map<String,String> param) throws ParseException, SQLException {
        Result res=new Result(ResultDetailMsg.OK);
        String start=param.get("incomeSettleBeginTime")+" 02:00:00";
        String endStr=param.get("incomeSettleEndTime")+" 01:59:59";
        Date end = DateFormat.date_add_or_sub_days(DateFormat.panDuanDate(endStr), 1);
        String sql="select ifnull(sum(clear_out_amount),0)*100 clear_out_amount,ifnull(sum(clear_out_paper_amount),0)*100 clear_out_paper_amount from view_sw_st_handwork_clean_record where " +
                "LINE_CODE='"+param.get("lineCode")+"' " +
                "and STATION_CODE= '"+param.get("stationCode")+"' " +
                "and TVM_CODE = '"+param.get("tvmCode")+"' " +
                "and CLEAN_TIME >='"+start+"' " +
                "and CLEAN_TIME <='"+DateFormat.date_ex_str(end,"yyyy-MM-dd HH:mm:ss")+"'";
        List<Map<String, Object>> maps = JdbcUtil.exeSqlQuery(sql);
        res.setData(maps);
        return res;
    }
	  swAuTvmBoxAmountDifference.setIncomeSettleBeginTime(settleStartTime);
            swAuTvmBoxAmountDifference.setIncomeSettleEndTime(settleEndTime);
			
			*/
			///  重新获取手工清出金额
//			Map<String,String> paramClearOut = new HashMap();
//            paramClearOut.put("incomeSettleBeginTime",DateFormat.date_ex_str(swAuTvmBoxAmountDifference.getIncomeSettleBeginTime(),"yyyy-MM-dd"));
//			paramClearOut.put("incomeSettleEndTime",DateFormat.date_ex_str(swAuTvmBoxAmountDifference.getIncomeSettleEndTime(),"yyyy-MM-dd"));
//			paramClearOut.put("lineCode",swAuTvmBoxAmountDifference.getLineCode());
//			paramClearOut.put("stationCode",swAuTvmBoxAmountDifference.getStationCode());
//			paramClearOut.put("tvmCode",swAuTvmBoxAmountDifference.getTvmCode());
			
           // BigDecimal clearOutHeJiAmount=new BigDecimal(0);
//            Result clearOutAmount = getClearOutAmount(paramClearOut);//需要重新组织参数。。。。。。。。。。
//            if("6".equals(cashBoxType)){ //硬币钱箱
//                BigDecimal clear_out_amount =(BigDecimal)((List<Map<String,Object>>) clearOutAmount.getData()).get(0).get("clear_out_amount");
//                clearOutHeJiAmount=swAuTvmBoxAmountDifference.getActualMoney().add(clear_out_amount);
//            }
//			if("1".equals(cashBoxType)){ //硬币回收箱
//                BigDecimal clear_out_amount =(BigDecimal)((List<Map<String,Object>>) clearOutAmount.getData()).get(0).get("clear_out_amount");
//                clearOutHeJiAmount=swAuTvmBoxAmountDifference.getActualMoney().add(clear_out_amount);
//            }
//			 if("2".equals(cashBoxType)){ //硬币找零箱
//               
//                clearOutHeJiAmount=swAuTvmBoxAmountDifference.getActualMoney();
//            }
//            if("3".equals(cashBoxType)){ //纸币回收
//                BigDecimal clear_out_paper_amount =(BigDecimal)((List<Map<String,Object>>) clearOutAmount.getData()).get(0).get("clear_out_paper_amount");
//                clearOutHeJiAmount=swAuTvmBoxAmountDifference.getActualMoney().add(clear_out_paper_amount);
//            }
//            if("4".equals(cashBoxType)){ //swAuTvmBoxAmountDifference
//                clearOutHeJiAmount=swAuTvmBoxAmountDifference.getActualMoney();
//            }
//			 if("5".equals(cashBoxType)){ //swAuTvmBoxAmountDifference
//                clearOutHeJiAmount=swAuTvmBoxAmountDifference.getActualMoney();
//            }
            //钱箱已经把手工清出金额重新获取了
            BigDecimal clearOutHeJiAmount=swAuTvmBoxAmountDifference.getActualMoney().add(swAuTvmBoxAmountDifference.getHandOutMoney());

            BigDecimal sellAmount =new BigDecimal(0);
			//判断是否是新增的-------------------------------
			//下面是老的
			if(isExist){
				//if(isChangeCashBox){//改变钱箱编号的再次提交，需要根据钱箱编号重新获取SC值
				    Map<String, Object> piaoKuanCY_box_sc = TvmUtil.getPiaoKuanCY_box_sc(param);
	                BigDecimal sc_unload_amount=(BigDecimal) piaoKuanCY_box_sc.get("amount");
	                BigDecimal supplement_amount=(BigDecimal) piaoKuanCY_box_sc.get("supplement_amount");
	                BigDecimal sc_hand_out_amount=new BigDecimal(0);
	                swAuTvmBoxAmountDifference.setScSaleAmount(sc_unload_amount.subtract(supplement_amount).add(sc_hand_out_amount));
	                swAuTvmBoxAmountDifference.setScUnloadAmount(sc_unload_amount);
	                swAuTvmBoxAmountDifference.setScSupplemntAmount(supplement_amount);
	                swAuTvmBoxAmountDifference.setScHandOutAmount(sc_hand_out_amount);
			    //}
			    if("1".equals(dataSource)){//人工修改
                    sellAmount=swAuTvmBoxAmountDifference.getBillOrderMoney();

				}else if("2".equals(dataSource)){//sc
					sellAmount=swAuTvmBoxAmountDifference.getScUnloadAmount();

				}else if("6".equals(dataSource)){//分钱箱票款差异协查
					sellAmount=swAuTvmBoxAmountDifference.getReplyActualMoney().add(swAuTvmBoxAmountDifference.getReplyHandOutMoney());

				}
			}else{//重新获取sc的值 不存在，重新获取SC等金额

	             Map<String, Object> piaoKuanCY_box_sc = TvmUtil.getPiaoKuanCY_box_sc(param);
	             BigDecimal sc_unload_amount=(BigDecimal) piaoKuanCY_box_sc.get("amount");
	             BigDecimal supplement_amount=(BigDecimal) piaoKuanCY_box_sc.get("supplement_amount");
	             BigDecimal sc_hand_out_amount=new BigDecimal(0);
	             swAuTvmBoxAmountDifference.setScSaleAmount(sc_unload_amount.subtract(supplement_amount).add(sc_hand_out_amount));
	             swAuTvmBoxAmountDifference.setScUnloadAmount(sc_unload_amount);
	             swAuTvmBoxAmountDifference.setScSupplemntAmount(supplement_amount);
	             swAuTvmBoxAmountDifference.setScHandOutAmount(sc_hand_out_amount); 
	             sellAmount = sc_unload_amount;
			
			}  			
            swAuTvmBoxAmountDifference.setSystemMoney(sellAmount);
            swAuTvmBoxAmountDifference.setDiffMoney(clearOutHeJiAmount.subtract(sellAmount));
			//----------------------------------------------------------------------------------------------------
         
            if(isExist){
                res=swAuTvmBoxAmountDifferenceMapper.updateById(swAuTvmBoxAmountDifference);
				/*if(isChangeCashBox){//钱箱编号发生变化，是否要更新协查等数据
				  //String sql1 = "update sw_au_tvm_money_assist set cash_box_code='"+changeOldCashBoxCode+"' where id="+swAuTvmBoxAmountDifference.getId()+" ";

				}*/
            }else{
                res=swAuTvmBoxAmountDifferenceMapper.insert(swAuTvmBoxAmountDifference);
            }
        }
        return res;
    }

	private static Map<String,Object> isChangeCashBoxNum(Map<String,Object> param){
	    Map<String,Object> map =null;
		String changeCashBoxCode = "";
		
	try{
		String lineCode = String.valueOf(param.get("line_code"));
        String stationCode = String.valueOf(param.get("station_code"));
        String tvmCode = String.valueOf(param.get("tvm_code"));
        Date record_time = DateFormat.panDuanDate(param.get("record_time"));   
		String recordTime = DateFormat.date_ex_str(record_time);
		String cashBoxType = String.valueOf(param.get("cash_box_type"));//钱箱类型
		String cashBoxCode = String.valueOf(param.get("cash_box_code"));//新的钱箱编号
		
		
		//可以改为Mapper的方式查找  ---TODO

		String sql = "select id,cash_box_code  from sw_au_tvm_box_amount_difference where line_code='"+lineCode+"' and station_code='"+stationCode+"' and tvm_code='"+tvmCode+"' and record_time='"+recordTime+"' and cash_box_type='"+cashBoxType+"' ";
      
	      List<Map<String, Object>> listMaps = JdbcUtil.exeSqlQuery(sql);
		  if(listMaps!=null && listMaps.size()>0){
		    Map mapQuery = listMaps.get(0);
			String cashBoxCodeOld = String.valueOf(mapQuery.get("cash_box_code"));
			if(!cashBoxCodeOld.equals(cashBoxCode)){
			   map  = new HashMap();
			   changeCashBoxCode = cashBoxCodeOld;
			   map.put("changeCashBoxCode",changeCashBoxCode);
			   map.put("id",(Integer)(mapQuery.get("id")));
			}
		  }	
	   }catch(Exception e){
	      
	   }
		
		return map;
	}

    /**
     * 查询分钱箱的数据
     * @param param
     * @return
     * @throws SQLException
     * @throws ParseException
     */
    public static List<Map<String, Object>> queryWillAdd_sw_au_tvm_box_amount_difference(Map<String,Object> param,String money_box_type) throws SQLException, ParseException {
        Object ids = param.get("ids");
        Object startSettleDate = param.get("startSettleDate");
        Object endSettleDate = param.get("endSettleDate");
        String startDate = DateFormat.ex_strDate_to_date(startSettleDate + "", "yyyy-MM-dd");
        String endDate = DateFormat.ex_strDate_to_date(endSettleDate + "", "yyyy-MM-dd");
        Object lineCode = param.get("lineCode");
        Object stationCode = param.get("stationCode");
        String queryCashBoxType  = "";
		   if(money_box_type.equals("sw_st_tvm_check_detail_coin")){//硬币回收箱
			    queryCashBoxType="1";
	        }else if(money_box_type.equals("sw_st_tvm_check_detail_coin_change")){//硬币找零箱
	        	queryCashBoxType="2";
	        }else if(money_box_type.equals("sw_st_tvm_check_detail_paper")){  //纸币回收箱
	        	queryCashBoxType="3";
	        }else if(money_box_type.equals("sw_st_tvm_check_detail_paper_change")){//纸币找零箱
	        	queryCashBoxType="4";
	        }else if(money_box_type.equals("sw_st_tvm_check_detail_waste")){//废钞箱
	        	queryCashBoxType="5";
	        }
        String sql= "select \n" +
                "0 relationid,\n" +
                "'#{startDate}' income_settle_begin_time,\n" +
                "'#{endDate}' income_settle_end_time,\n" +
                "scd.INCOME_SETTLE_ID income_settle_id,\n" +
                "scd.CASH_BOX_RELATION_ID cash_box_relation_id,\n" +
                "\tscd.LINE_CODE line_code,\n" +
                "  scd.STATION_CODE station_code,\n" +
                "  scd.TVM_CODE tvm_code,\n" +
                " "+queryCashBoxType+"  cash_box_type,\n"+
                //"scd.CASHBOX_CODE cash_box_code,\n" +
                "case scd.UNLOAD_CASE when 3 then 1 else 2 end as box_uninstall_status,\n"+
				"0.00 system_money,\n" +
                "0.00  bill_order_money,\n"+
                "0 sc_sale_amount,\n" +
                "0 sc_unload_amount,\n" +
                "0 sc_hand_out_amount,\n" +
                "0 sc_supplemnt_amount,\n" +
                "ifnull(scd.SYS_AMOUNT,0)*100 unload_display_money,\n" +
                "ifnull(scd.REAL_AMOUNT,0)*100 actual_money,\n" ;
              
		       if(queryCashBoxType.equals("1")){//硬币回收箱
		            sql+= "ifnull(vsc2.CLEAR_OUT_AMOUNT,0)*100 hand_out_money,\n" +
		                  "( ifnull(scd.REAL_AMOUNT,0) + ifnull(vsc2.CLEAR_OUT_AMOUNT,0))*100 as diff_money,\n"; 
		            //if(!lineCode.equals("1")){//如果是硬币回收 1，且线路编号不为1  ，取补币表中钱箱类型为2的补币金额
		         	   sql+="ifnull(vsc.SUPPLEMENT_AMOUNT,0)*100 supplement_money,\n"; //硬币补币
		         	   
		            /*}else{
		         	   sql+="0.00 supplement_money,\n";
		            }*/
		      }else if(queryCashBoxType.equals("2")){ //硬币找零箱
		             sql+= "0.00 hand_out_money,\n" +
		                   "ifnull(scd.REAL_AMOUNT,0)*100 diff_money,\n"+
		                   //"ifnull(vsc.SUPPLEMENT_AMOUNT,0)*100 supplement_money,\n"; //硬币找零,所有都不算补币金额
					       "0.00 supplement_money,\n";
		      }else if(queryCashBoxType.equals("3")){ //纸币回收箱
		             sql+= "ifnull(vsc2.CLEAR_OUT_PAPER_AMOUNT,0)*100 hand_out_money,\n" +
		                  "( ifnull(scd.REAL_AMOUNT,0) + ifnull(vsc2.CLEAR_OUT_PAPER_AMOUNT,0))*100 as diff_money,\n" +
		                  "0.00 supplement_money,\n";
		      }else if(queryCashBoxType.equals("4")){ //纸币找零箱
		             sql+=  "0.00 hand_out_money,\n" +
		                    "ifnull(scd.REAL_AMOUNT,0)*100 diff_money,\n"+
		                    "ifnull(vsc.SUPPLEMENT_AMOUNT,0)*100 supplement_money,\n"; //纸币找零箱补币
		      }else if(queryCashBoxType.equals("5")){ //废钞箱
		             sql+=  "0.00 hand_out_money,\n" +
		                   "ifnull(scd.REAL_AMOUNT,0)*100 diff_money,\n"+
		                   "0.00 supplement_money,\n";
		      }
           
        sql+="scd.RECORD_TIME record_time,\n" +
                "2 data_source,\n" +
                "0 audit_status,\n" +
                "0 assist_invest_status,\n" +
                "'' diff_reason,\n" +
                "0 is_add_assist,\n" +
                "0 is_add_level_assist,\n" +
                ""+ UserInfo.getId() +" creator_id,\n" +
                "'"+ UserInfo.getName() +"' creator_name,\n" +
                "now() create_time\n" +
                "from (select \n" +
                "b.id sstcdId,"+
                "scd.ID INCOME_SETTLE_ID,\n" +
                "a.ID CASH_BOX_RELATION_ID,\n" +
                "\tscd.LINE_CODE,\n" +
                "  scd.STATION_CODE,\n" +
                "  scd.TVM_CODE,\n" +
                //"\ta.CASHBOX_CODE,\n" +
                "a.UNLOAD_STATUS*1 UNLOAD_STATUS,\n" +
                "a.UNLOAD_CASE UNLOAD_CASE,\n" +

                "a.SYS_AMOUNT,\n" +
                "a.REAL_AMOUNT,\n" +
                "scd.RECORD_TIME\n" +
                "from "+money_box_type+" a,sw_st_tvm_check_detail b,SW_ST_TVM_INCOME_SETTLE scd \n" +
                "where (scd.settle_status <> 2 or scd.SETTLE_STATUS is null)\n" +
                "        \tand scd.line_code='#{lineCode}'\n" +
                "        \tand scd.station_code='#{stationCode}'\n" +
                "        \tand date_add(scd.RECORD_TIME,interval 12 hour) between '#{startSettleDate}' and '#{endSettleDate}'\n" +
                "        \tand scd.DEL_FLAG = '0'\n" +
                "        \tand a.DEL_FLAG = '0'\n" +
                "        \tand b.DEL_FLAG = '0'\n" +
                "        \tand scd.line_code=b.line_code and scd.station_code=b.station_code and scd.tvm_code = b.tvm_code\n" +
                "\t\t\t\t\tand date_add(b.revenue_time,interval 12 hour) between '#{startSettleDate}' and '#{endSettleDate}'\n" +
                "        \tand b.ID = a.RELATION_ID and a.del_flag = '0') scd\n" ;
                /*  2021-03-16  补币问题处理
				  如果是纸币找零 4  ，取补币表中钱箱类型为4的补币金额
				  如果是硬币找零 2  ，取补币表中钱箱类型为2的补币金额 
                如果是硬币回收 1，且线路编号不为1  ，取补币表中钱箱类型为2的补币金额
				  and scd.CASHBOX_CODE = vsc.TVM_CASH_BOX_NUM 换成钱箱类型条件，group by TVM_CASH_BOX_NUM和select中 条件去掉

				  分钱箱审核里面硬币找零箱，除1号线外其余线路均无硬币找零箱

				   2021-04-11  调整为所有的硬币补币都算到硬币回收箱内，纸币算到纸币回收箱内。
				*/

				if(queryCashBoxType.equals("1")){// && !lineCode.equals("1")){//硬币回收箱
					  sql+= " left join \n" +
						    "        (select LINE_CODE,STATION_CODE,TVM_CODE,TVM_CASH_BOX_TYPE, sum(SUPPLEMENT_AMOUNT) SUPPLEMENT_AMOUNT\n" +
							"        \tfrom view_sw_st_tvm_supplement_currency \n" +
							"\t\t\t\t\twhere SUPPLEMENT_CURRENCY_TIME between '#{startSettleDate}' and '#{endSettleDate}'  and TVM_CASH_BOX_TYPE='2'\n" +
							"        \tgroup by line_code,station_code,tvm_code,TVM_CASH_BOX_TYPE) vsc \n"+
							"     on scd.line_code=vsc.line_code and scd.station_code=vsc.station_code and scd.tvm_code = vsc.tvm_code  and vsc.TVM_CASH_BOX_TYPE='2' \n" ;
	                   
	             }else if(queryCashBoxType.equals("2")){ //硬币找零箱
	            	  /*
					  sql+= " left join \n" +
							 "        (select LINE_CODE,STATION_CODE,TVM_CODE,TVM_CASH_BOX_TYPE,sum(SUPPLEMENT_AMOUNT) SUPPLEMENT_AMOUNT\n" +
							 "        \tfrom view_sw_st_tvm_supplement_currency \n" +
							"\t\t\t\t\twhere SUPPLEMENT_CURRENCY_TIME between '#{startSettleDate}' and '#{endSettleDate}' and  TVM_CASH_BOX_TYPE='2' \n" +
							"        \tgroup by line_code,station_code,tvm_code,TVM_CASH_BOX_TYPE) vsc \n"+
							"     on scd.line_code=vsc.line_code and scd.station_code=vsc.station_code and scd.tvm_code = vsc.tvm_code  and vsc.TVM_CASH_BOX_TYPE='2' \n" ;
					   */
	             }else if(queryCashBoxType.equals("3")){ //纸币回收箱
	                    
	             }else if(queryCashBoxType.equals("4")){ //纸币找零箱
	            	 sql+= " left join \n" +
							 "        (select LINE_CODE,STATION_CODE,TVM_CODE,TVM_CASH_BOX_TYPE,sum(SUPPLEMENT_AMOUNT) SUPPLEMENT_AMOUNT\n" +
							 "        \tfrom view_sw_st_tvm_supplement_currency \n" +
							"\t\t\t\t\twhere SUPPLEMENT_CURRENCY_TIME between '#{startSettleDate}' and '#{endSettleDate}' and  TVM_CASH_BOX_TYPE='4' \n" +
							"        \tgroup by line_code,station_code,tvm_code,TVM_CASH_BOX_TYPE) vsc \n"+
							"     on scd.line_code=vsc.line_code and scd.station_code=vsc.station_code and scd.tvm_code = vsc.tvm_code  and vsc.TVM_CASH_BOX_TYPE='4' \n" ;
	             }else if(queryCashBoxType.equals("5")){ //废钞箱
	                  
	             }
		        if(queryCashBoxType.equals("1")|| (queryCashBoxType.equals("3"))){//手工清出金额算到硬币回收箱和纸币回收箱内
					
					sql+= " left join \n" +
			                "        (select LINE_CODE,STATION_CODE,TVM_CODE,sum(CLEAR_OUT_AMOUNT) CLEAR_OUT_AMOUNT," +
			                "           sum(CLEAR_OUT_PAPER_AMOUNT) CLEAR_OUT_PAPER_AMOUNT\n" +
			                "        \tfrom view_sw_st_handwork_clean_record \n" +
			                "\t\t\t\t\twhere CLEAN_TIME between '#{startSettleDate}' and '#{endSettleDate}'\n" +
			                "        \tgroup by line_code,station_code,tvm_code) vsc2 on scd.line_code=vsc2.line_code and scd.station_code=vsc2.station_code and scd.tvm_code = vsc2.tvm_code\n" ;
					
				}     
                
                                
		        sql+="\nwhere scd.sstcdId in ("+ids+")";
        sql=sql.replace("#{startDate}",startDate).replace("#{endDate}",endDate);
        sql=sql.replace("#{startSettleDate}",startSettleDate+"").replace("#{endSettleDate}",endSettleDate+"");
        sql=sql.replace("#{lineCode}",lineCode+"").replace("#{stationCode}",stationCode+"");
        LOGGER.info("查詢分钱箱审核主表预处理SQL----"+sql);
        List<Map<String, Object>> maps = JdbcUtil.exeSqlQuery(sql);
        return maps;
    }

	    /**
     * 获取手工清出金额
     * @param param
     * @return
     * @throws ParseException
     * @throws SQLException
     */
    /*
    private static Result getClearOutAmount(Map<String,String> param) throws ParseException, SQLException {
        Result res=new Result(ResultDetailMsg.OK);
        String start=param.get("incomeSettleBeginTime")+" 02:00:00";
        String endStr=param.get("incomeSettleEndTime")+" 01:59:59";
        Date end = DateFormat.date_add_or_sub_days(DateFormat.panDuanDate(endStr), 1);
        String sql="select ifnull(sum(clear_out_amount),0)*100 clear_out_amount,ifnull(sum(clear_out_paper_amount),0)*100 clear_out_paper_amount from view_sw_st_handwork_clean_record where " +
                "LINE_CODE='"+param.get("lineCode")+"' " +
                "and STATION_CODE= '"+param.get("stationCode")+"' " +
                "and TVM_CODE = '"+param.get("tvmCode")+"' " +
                "and CLEAN_TIME >='"+start+"' " +
                "and CLEAN_TIME <='"+DateFormat.date_ex_str(end,"yyyy-MM-dd HH:mm:ss")+"'";
        List<Map<String, Object>> maps = JdbcUtil.exeSqlQuery(sql);
        res.setData(maps);
        return res;
    }*/
}
